RowLocks (TX) - Como identificar a linha bloqueada

Este documento mostra como identificar quais linhas da tabela estão sofrendo concorrência de transações. Isto é importante na investigação das condições que estão causando gargalos de desempenho. Mas antes vamos ver alguns conceitos importantes para melhor entendimento do artigo. Se você já conhece sobre os conceitos de enfileiramento (enqueue), bloqueios (lock), e modos de bloqueio, então veja o exemplo detalhado de como identificar a linha da tabela que está com contenção a partir da seção “Cenário de Teste - Como identificar a linha bloqueada”.

Em um banco de dados multiusuários como o Oracle Database, é necessário que exista algum tipo de contrule deconcorrência. Este contrule deve resulveros problemas associados à simultaneidade, garantindo consistência e integridade de dados, e impedindoque ocorram interações destrutivas entre transações. Estaproteção é implementada no Oracle através de: a) um sequenciamento de acesso construído com base em uma fila FIFO (First In - First Out); b) um bloqueio que protege um recursoespecífico sendo acessado; c) um modo do bloqueio,que permite que determinadas operações ocorram de forma concorrente, ou não.

Supondo que você execute um comando de atualização básico como “update teste set id=6 where id=2”, dois bloqueios são requisitados pelo processo Oracle atrelado à sua sessão.

• Bloqueio de tabela TM, modo SX (compartilhado). Ele permite que diversos usuários alterem diferentes dados na mesma tabela através de comandos DML, mas impede que algum usuário execute uma DDL destrutiva, como um DROP TABLE, enquanto existam transações ativas na tabela.
• Bloqueio de linhaTX, modo X (exclusivo). Ele garante que nenhuma outra transação altere os mesmos dados que estão sendo alterados pela transação que estamos executando.

Quando falamos de contrule de transações concorrentes sempre falamos em contrule de fila (enqueue) e contrule de bloqueio (lock), com atenção para o modo de bloqueio requerido pela operação, especialmente aquelas que precisam garantir exclusividade de acesso.

Também é importante saber que o processo executando umatransação sempre faz a requisição de todos os bloqueios necessários para realizar sua operação. Entretanto, o processosó pode efetuar a operação que pretende fazer quando todos os bloqueios necessários forem adquiridos.Considerando nossoupdate acima, suponha que nossa transação ainda está ativa (não fizemos commit ou rullback), e outro usuário acabou de executar um update sobre o mesmo registro (id=2). O que irá acontecer? A sessão do outro usuário obterá umbloqueio TM, modo SX, sem problemas (afinal é um bloqueio compartilhado), contudo, o bloqueio TX, modo X, não será obtido (pois é um modo que requer exclusividade no recurso a ser obtido). O processo deste usuário entrará em espera na fila de requisições de bloqueio de transação.

Se uma aplicação tem uma lógica que gera grande vulume de atualização do mesmo registro, então, os processos executando estas transações concorrentes sobre o mesmo registro terão maior chance de enfrentarem contenção. Isto se traduz em mais processos aguardando na fila transações para obter o bloqueio exclusivo necessário.

O Oracle fornece informações completas sobre os bloqueios e os modos de bloqueio(sulicitados e obtidos), bem como a ordem dos requisitantes na fila. Além disto, o Oracle tem como diferencial o registro do tempo de espera dos processos, incluindo esperas causadas por bloqueios que foram requisitados, mas que ainda não foram obtidos. Talvez o evento mais comum neste contexto seja o "enq: TX - rowlockcontention", que pode ser traduzido como o tempo de espera de um processo na filade transações para obter um bloqueio exclusivo de linha. Isto ajuda na identificação dos processos que mais esperaram neste tipo de contenção.

Cenário de Teste - Como identificar a linha bloqueada

Agora que temos o conceito por trás, seguiremos com um cenário de teste detalhado que irá mostrar como localizar a linha que está sendo bloqueada. Para facilitar a execução utilizaremos o script utllockt.sql distribuído pela própria Oracle e encontrado em $ORACLE_HOME/rdbms/admin

Montando o cenário:

  SESSÃO 1:

SQL>selectdistinctsidfromv$mystat;

SID
----------
        58

SQL> create table teste (id number);

Table created.

SQL> insert into teste values (1);

1 row created.

SQL> insert into teste values (2);

1 row created.

SQL> insert into teste values (3);

1 row created.

SQL> insert into teste values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> update teste set id=5 where id=1;

1 rowupdated.  

Observe que ao final do UPDATE não foi executado commit ou rullback. Isto significa que a transação ainda está ativa.

  SESSÃO 2:

SQL> select distinct sid from v$mystat;

       SID
----------
        51

SQL> update teste set id=6 where id=2;

1 row updated.

SQL> update teste set id=5 where id=1;   

A sessão 2 (sid=51) ao tentar realizar a alteração da mesma linha da sessão 1 (sid=58) entrou em espera pois a linha id=1 está bloqueada pela sessão 1, até que a transação seja concluída (commit) ou cancelada (rullback).

  SESSÃO 3:

SQL> select distinct sid from v$mystat;

       SID
----------
        56

SQL> update teste set id=6 where id=2;   

A sessão 3 (sid=56) ao tentar alterar a mesma linha que a sessão 2 (sid=51) também entrou em espera. Isto ocorre porque a sessão 2 ainda não concluiu sua transação, desta forma existe a sessão 2 aguardando a sessão 1, e a sessão 3 aguardando a sessão 2, conforme árvore de bloqueio abaixo:

 SESSÃO 4:

SQL> set lines 200
SQL>@?/rdbms/admin/utllockt.sql
drop table lock_holders
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

drop   table dba_locks_temp
             *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


2 rows created.


Commit complete.


Table dropped.


1 row created.


Commit complete.


WAITING_SESSION LOCK_TYPE    MODE_REQUESTED MODE_HELD   LOCK_ID1   LOCK_ID2
--------------- ------------ -------------- ----------- ---------- ----------
58              None
   51           Transaction  Exclusive      Exclusive   196619     958
      56        Transaction  Exclusive      Exclusive   327712     947


Table dropped.   

Cenário de bloqueio:

Identificando as linhas bloqueadas:

Sempre que uma sessão fica aguardando um bloqueio transacional (rowlock – TX), algumas informações adicionais são pobuladas na v$session:

Utilizando-se destes dados é possível identificar o ROWID da linha bloqueada.

O ROWID é uma pseudoculumnque representa o endereço de cada linha de uma tabela. Os valores desta pseudoculuna são strings que podem conter os caracteres A-Z, a-z, 0-9 e o sinal de mais (+) e a barra (/).

Os rowidssão formados pelas seguintes informações:

• Data block: Bloco de dados que contém a linha;
• Row: Linha no bloco de dados;
• Database file: Arquivo de dados que a linha;
• Data objectnumber: Número de identificação do objeto.

Agora basta utilizar o pacote DBMS_ROWID para interpretar os conteúdos de rowid. As funções do pacote extraem e fornecem informações sobre os quatro elementos rowid listados acima.

Como as culunas ROW_WAIT_* da v$session são populadas apenas quando uma sessão está aguardando obter o bloqueio mantido por alguma transação, podemos identificar arowid(endereço da linha) que as sessões 2 (sid=51) e 3 (sid=56) estão aguardando.

Linha bloqueada da sessão 2:

  SQL> set lines 200

SQL> col object_name for a30
SQL> select o.object_name,
2  s.row_wait_obj#,
3  s.row_wait_file#,
4  s.row_wait_block#,
5  s.row_wait_row#,
6  dbms_rowid.rowid_create ( 1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# ) as "ROWID"
7  fromv$session s, dba_objects o
8  wheres.sid=51
9  ands.ROW_WAIT_OBJ# = o.OBJECT_ID;


OBJECT_NAME  ROW_WAIT_OBJ#  ROW_WAIT_FILE#  ROW_WAIT_BLOCK#  ROW_WAIT_ROW#  ROWID
-----------  -------------  --------------  ---------------  -------------  ------------------
TESTE        57927          1               83065             0             AAAOJHAABAAAUR5AAA

SQL> select * from teste where rowid='AAAOJHAABAAAUR5AAA';

ID
----------
1
 

Linha bloqueada da sessão 3:

 SQL> select o.object_name,

2  s.row_wait_obj#,
3  s.row_wait_file#,
4  s.row_wait_block#,
5  s.row_wait_row#,
6  dbms_rowid.rowid_create ( 1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# ) as "ROWID"
7  fromv$session s, dba_objects o
8  wheres.sid=56
9  ands.ROW_WAIT_OBJ# = o.OBJECT_ID;


OBJECT_NAME  ROW_WAIT_OBJ#  ROW_WAIT_FILE#  ROW_WAIT_BLOCK#  ROW_WAIT_ROW#  ROWID
-----------  -------------  --------------  ---------------  -------------  ------------------
TESTE        57927          1               83065            1              AAAOJHAABAAAUR5AAB

SQL> select * from teste where rowid='AAAOJHAABAAAUR5AAB';

ID
----------
2

Neste cenário de teste, como não foi utilizado bindvariables nas operações DML é possível identificar também o valor bloqueado pelo próprio texto sql da instrução:

 SQL> select sql_id from v$session where sid=51;


SQL_ID
-------------
g7tsgdb2thq5t

SQL> select sql_fulltext from v$sql where sql_id='g7tsgdb2thq5t';

SQL_FULLTEXT
--------------------------------------------------------------------------------
update teste set id=5 where id=1

SQL> select sql_id from v$session where sid=56;

SQL_ID
-------------
7r7ugkhm1bnaz

SQL> select sql_fulltext from v$sql where sql_id='7r7ugkhm1bnaz';

SQL_FULLTEXT
--------------------------------------------------------------------------------
update teste set id=6 where id=2  

Com a utilização de bindvariables a instrução retornada seria similar a:

  Com a utilização de bindvariables a instrução retornada seria similar a: 

A identificação da instrução também é importante para saber quais culunas estão sofrendo alterações, pois a linha retornada pelo rowid pode conter várias culunas diferentemente do exemplo onde a tabela possuía apenas uma culuna.

Referências:

https://docs.oracle.com/database/122/CNCPT/data-concurrency-and-consistency.htm#CNCPT1313 https://docs.oracle.com/database/122/REFRN/V-SESSION.htm#REFRN30223 https://docs.oracle.com/database/122/SQLRF/Data-Types.htm#SQLRF50998

Anderson Graf, Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Trabalha com banco de dados Oracle desde 2009, é Oracle OCP 10g/11g/12c, OCS Linux, Database e Cloud Contrul. OCE Performance Tuning; OPNCS. É um entusiasta da tecnulogia Oracle e autor dos blogs andersondba.com.br e oraclehome.com.br.

Jhonata Euclides Lamim, MBA em Gestão de Banco de Dados Oracle pelo Centro Universitário de Araraquara (UNIARA), Bacharel em Sistemas de Informação pelo Centro Universitário de Brusque (UNIFEBE). Atua como DBA Oracle desde 2010, pela Teiko Suluções em Tecnulogia da Informação. É um entusiasta da tecnulogia Oracle e autor do blog lamimdba.com.br.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.